"""
Case Type   : 事务控制
Case Name   : 匿名块中直接执行DDL是否成功
Create At   : 2020/12/02 
Owner       : opentestcase015
Description :
    1.查看测试表是否存在，若存在，则删除
    2.开启事务并执行只含有DDL语句的匿名块后进行commit提交
    3.查看备机数据是否同步
Expect      :
    1.删除已有测试表成功
    2.执行只含有DDL语句的匿名块成功
    3.查看备机数据同步成功
History     :
    2020/12/14 modifi by opentestcase007 类名与方法名重命名为与用例相关的名称，修改清理环境步骤
    2021/9/2   modified by opentestcase001 优化用例，增加等待主备同步防止备节点未同步
    2024/6/18 modified by peilinqian 用例适配资源池化
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()
primary_sh = CommonSH('PrimaryDbUser')


class TransactionFile(unittest.TestCase):
    def setUp(self):
        logger.info('----Opengauss_Function_DML_Transaction_Case0002开始执行----')
        self.PrimaryNode = Node('PrimaryDbUser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.Constant = Constant()
        self.Common = Common()
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH

    def test_transaction_file(self):
        logger.info('------若为单机环境，后续不执行，直接通过------')
        excute_cmd = f''' source {self.DB_ENV_PATH};
            gs_om -t status --detail'''
        logger.info(excute_cmd)
        msg = self.PrimaryNode.sh(excute_cmd).result()
        logger.info(msg)
        if 'Standby' not in msg:
            return '单机环境，后续不执行，直接通过'
        else:
            self.StandbyNode = Node('Standby1DbUser')
            logger.info('------新建测试表------')
            sql_cmd = 'drop table if exists testzl;'
            excute_cmd = f'''source {self.DB_ENV_PATH};
                gsql -d {self.PrimaryNode.db_name} \
                -p {self.PrimaryNode.db_port} \
                -c "{sql_cmd}"'''
            logger.info(excute_cmd)
            msg = self.PrimaryNode.sh(excute_cmd).result()
            logger.info(msg)
            self.assertNotIn(self.Constant.SQL_WRONG_MSG[1], msg)

            logger.info('------开启事务并执行只含有DDL语句的匿名块------')
            sql_cmd = f'''start transaction;
                declare 
                begin
                    create table testzl 
                    (SK INTEGER,ID CHAR(16),NAME VARCHAR(20),SQ_FT INTEGER);
                end;
                commit;'''
            excute_cmd = f'''source {self.DB_ENV_PATH} ;
                gsql -d {self.PrimaryNode.db_name} \
                -p {self.PrimaryNode.db_port} \
                -c "{sql_cmd}"'''
            logger.info(excute_cmd)
            msg = self.PrimaryNode.sh(excute_cmd).result()
            logger.info(msg)
            self.assertIn(self.Constant.COMMIT_SUCCESS_MSG, msg)

            logger.info('------查看备机数据是否同步-------')
            sql_cmd = f'''select count(*) from testzl;'''
            excute_cmd = f'''source {self.DB_ENV_PATH};
                gsql -d {self.StandbyNode.db_name} \
                -p {self.StandbyNode.db_port} \
                -c "{sql_cmd}"'''
            logger.info(excute_cmd)
            msg = self.StandbyNode.sh(excute_cmd).result()
            logger.info(msg)
            res = msg.splitlines()[-2].strip()
            self.assertIn('0', res)

    def tearDown(self):
        logger.info('------清理环境------')
        sql_cmd = 'drop table if exists testzl;'
        excute_cmd = f'''source {self.DB_ENV_PATH};
            gsql -d {self.PrimaryNode.db_name} \
            -p {self.PrimaryNode.db_port} \
            -c "{sql_cmd}"'''
        logger.info(excute_cmd)
        msg = self.PrimaryNode.sh(excute_cmd).result()
        logger.info(msg)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, msg)
        logger.info('----Opengauss_Function_DML_Transaction_Case0002执行完成----')
